package com.cttic.erp.controller;

import java.io.File;
import java.io.PrintWriter;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONObject;

import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.cttic.erp.bean.BusiEmployeeVacationRecordBean;
import com.cttic.erp.bean.InfoDateBean;
import com.cttic.erp.bean.InfoEmployeeBean;
import com.cttic.erp.bean.SysParamBean;
import com.cttic.erp.bean.ext.InfoOperBeanExt;
import com.cttic.erp.common.constants.BusiConstants;
import com.cttic.erp.common.util.CacheUtil;
import com.cttic.erp.common.util.ExcelUtil;
import com.cttic.erp.common.util.ExceptionUtil;
import com.cttic.erp.common.util.InfoOperUtil;
import com.cttic.erp.common.util.StringUtil;
import com.cttic.erp.service.interfaces.IBusiEmployeeVacationRecordService;
import com.cttic.erp.service.interfaces.IInfoDateService;
import com.cttic.erp.service.interfaces.IInfoEmployeeService;
import com.cttic.erp.service.interfaces.IMySQLDateService;

@Controller
@RequestMapping(value = "/vacation")
public class VacationController {

	@Resource
	IBusiEmployeeVacationRecordService busiEmployeeVacationRecordServiceImpl;
	
	@Resource
	private IInfoEmployeeService infoEmployeeServiceImpl;
	
	@Resource
	private IMySQLDateService mySQLDateServiceImpl;
	
	@Resource
	private IInfoDateService infoDateService;
	
	@RequestMapping(value = "/apply")
	public ModelAndView apply(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap) throws Exception {
		// 1、获取当前操作员信息
		InfoOperBeanExt infoOperBean = InfoOperUtil.getCurrOperInfo(request);
		// 2、获取人员信息
		InfoEmployeeBean infoEmployeeBean = infoOperBean.getInfoEmployeeBean();
		modelMap.put("SELECT_EMPLOYEE", infoEmployeeBean);
		// 3、获取请假类型列表
		modelMap.put("VACATION_TYPE", getVacationType());
		// 4、获取休假地列表
		modelMap.put("VACATION_PLACE", getVacationPlace());
		// 5、获取请假状态列表
		modelMap.put("VACATION_STATE", getVacationState());
		// 6、获取请假类型列表
		modelMap.put("VACATION_MARK_TYPE", getVacationMarkType());
		// 7、获取月份列表
		modelMap.put("REPORT_MONTH_LIST", getReportMonthList());
		// 8、获取当前月份
		String currDate = mySQLDateServiceImpl.getSysdateStr();
		String reportMonth = request.getParameter("REPORT_MONTH");
		String currYearStr = currDate.split("-")[0];
		String currMonthStr = currDate.split("-")[1];
		if (!StringUtil.isBlank(reportMonth)) {
			currDate = reportMonth;
			currYearStr = currDate.substring(0, 4);
			currMonthStr = currDate.substring(5, 6);
		}
		
		int currYear = Integer.parseInt(currYearStr);
		int currMonth = Integer.parseInt(currMonthStr);
		SysParamBean sysParamBean = new SysParamBean();
		sysParamBean.setType_code("REPORT");
		sysParamBean.setParam_code("MONTH_LIST");
		String monthStr = "";
		if (String.valueOf(currMonth).length() == 1) {
			monthStr = "0" + currMonth;
		} else {
			monthStr = "" + currMonth;
		}
		sysParamBean.setColumn_value(currYear + monthStr);
		sysParamBean.setColumn_desc(currYear + "年" + monthStr + "月");
		modelMap.put("REPORT_CURR_MONTH", sysParamBean);
		// 9、获取当前月的天列表
		modelMap.put("DATE_LIST", infoDateService.getBeans(currYearStr, monthStr));
		// 10、获取休假记录
		List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeans(infoEmployeeBean.getEmployee_id(), currYearStr + monthStr);
		modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
		return new ModelAndView("vacation/apply2", modelMap);
	}
	
	@RequestMapping(value = "/save")
	public void save(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		JSONObject rtnJson = new JSONObject();
		try {
			// 1、获取当前操作员信息
			InfoOperBeanExt infoOperBean = InfoOperUtil
					.getCurrOperInfo(request);
			long employeeId = infoOperBean.getInfoEmployeeBean()
					.getEmployee_id();
			// 2、获取页面操作标示
			String operType = request.getParameter("OPER_TYPE");
			// 3、获取页面提交信息
			BusiEmployeeVacationRecordBean busiEmployeeVacationRecordBean = new BusiEmployeeVacationRecordBean();
			if ("ADD".equals(operType) || "EDIT".equals(operType)) {
				String type = request.getParameter("TYPE");
				String beginDate = request.getParameter("BEGIN_DATE");
				String endDate = request.getParameter("END_DATE");
				String numberOfDays = request.getParameter("NUMBER_OF_DAYS");
				String place = request.getParameter("PLACE");
				String reason = request.getParameter("REASON");
				busiEmployeeVacationRecordBean.setEmployee_id(employeeId);
				busiEmployeeVacationRecordBean.setType(type);
				busiEmployeeVacationRecordBean.setBegin_date(beginDate);
				busiEmployeeVacationRecordBean.setPlace(place);
				busiEmployeeVacationRecordBean.setReason(reason.replaceAll("\n", "<br>"));
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				Calendar currDate = mySQLDateServiceImpl.getSysdate();
				if (dateFormat.parse(beginDate).before(currDate.getTime())) {
					throw new Exception("起始日期[" + beginDate + "]已过期，当前日期为[" + dateFormat.format(currDate.getTime()) + "]");
				}
				// 4、根据操作类型进行判断：如果为'ADD'，则为新增操作
				if ("ADD".equals(operType)) {
					busiEmployeeVacationRecordBean.setState("0");
					// 4.1、验证休假日期是否重复
					List<BusiEmployeeVacationRecordBean> beanList = busiEmployeeVacationRecordServiceImpl.getBeansByEmployeeId(employeeId);
					for (BusiEmployeeVacationRecordBean bean : beanList) {
						if (!"1".equals(bean.getState()) && !"9".equals(bean.getState())) {
							String _beginDate = bean.getBegin_date();
							String _endDate = bean.getEnd_date();
							// 1)判断开始日期是否于已有休假记录的开始日期一样
							if (beginDate.equals(_beginDate)) {
								throw new Exception("起始日期[" + beginDate + "]已有休假申请");
							}
							// 2)判断开始日期是否于已有休假记录的截止日期一样
							if (beginDate.equals(_endDate)) {
								throw new Exception("起始日期[" + beginDate + "]已有休假申请");
							}
							// 3)判断开始日期是否介于已有休假记录的起始、截止日期之间
							if (dateFormat.parse(beginDate).after(dateFormat.parse(_beginDate)) && dateFormat.parse(beginDate).before(dateFormat.parse(_endDate))) {
								throw new Exception("起始日期[" + beginDate + "]已有休假申请");
							}
							// 4)判断截止日期是否于已有休假记录的开始日期一样
							if (endDate.equals(_beginDate)) {
								throw new Exception("截止日期[" + endDate + "]已有休假申请");
							}
							// 5)判断截止日期是否于已有休假记录的截止日期一样
							if (endDate.equals(_endDate)) {
								throw new Exception("截止日期[" + endDate + "]已有休假申请");
							}
							// 6)判断截止日期是否介于已有休假记录的起始、截止日期之间
							if (dateFormat.parse(endDate).after(dateFormat.parse(_beginDate)) && dateFormat.parse(endDate).before(dateFormat.parse(_endDate))) {
								throw new Exception("截止日期[" + endDate + "]已有休假申请");
							}
						}
					}
					if (Float.parseFloat(numberOfDays) > 1) {
						String nextDate = beginDate;
						for (int cnt = 0; cnt < Integer.parseInt(numberOfDays); cnt ++) {
							if (cnt == 0) {
								busiEmployeeVacationRecordBean.setEnd_date(beginDate);
							} else {
								nextDate = mySQLDateServiceImpl.getNextDate(nextDate);
								busiEmployeeVacationRecordBean.setBegin_date(nextDate);
								busiEmployeeVacationRecordBean.setEnd_date(nextDate);
							}
							if ("21".equals(type) || "31".equals(type) || "41".equals(type) || "51".equals(type)
							 || "61".equals(type) || "71".equals(type) || "81".equals(type)) {
								busiEmployeeVacationRecordBean.setNumber_of_days("1");
							} else if ("22".equals(type) || "23".equals(type) || "32".equals(type) || "33".equals(type)){
								busiEmployeeVacationRecordBean.setNumber_of_days("0.5");
							}
							busiEmployeeVacationRecordServiceImpl.insert(busiEmployeeVacationRecordBean);
						}
					} else {
						busiEmployeeVacationRecordBean.setEnd_date(endDate);
						busiEmployeeVacationRecordBean.setNumber_of_days(numberOfDays);
						busiEmployeeVacationRecordServiceImpl.insert(busiEmployeeVacationRecordBean);
					}
				} else if ("EDIT".equals(operType)) {
					String recordId = request.getParameter("RECORD_ID");
					busiEmployeeVacationRecordBean.setRecord_id(Long.parseLong(recordId));
					busiEmployeeVacationRecordServiceImpl.updateRecord(busiEmployeeVacationRecordBean);
				} 
			} else if ("CANCEL".equals(operType) || "AUDIT".equals(operType)) {
				String recordId = request.getParameter("RECORD_ID");
				busiEmployeeVacationRecordBean.setRecord_id(Long.parseLong(recordId));
				if ("CANCEL".equals(operType)) {
					busiEmployeeVacationRecordBean = busiEmployeeVacationRecordServiceImpl.getBeansByRecordId(Long.parseLong(recordId));
					DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
					Calendar currDate = mySQLDateServiceImpl.getSysdate();
					if (dateFormat.parse(busiEmployeeVacationRecordBean.getBegin_date()).before(currDate.getTime())) {
						throw new Exception("起始日期[" + busiEmployeeVacationRecordBean.getBegin_date() + "]已过期，当前日期为[" + dateFormat.format(currDate.getTime()) + "]，不能进行撤销操作");
					}
					if (dateFormat.parse(busiEmployeeVacationRecordBean.getEnd_date()).before(currDate.getTime())) {
						throw new Exception("截止日期[" + busiEmployeeVacationRecordBean.getEnd_date() + "]已过期，当前日期为[" + dateFormat.format(currDate.getTime()) + "]，不能进行撤销操作");
					}
					busiEmployeeVacationRecordBean.setState("9");
					busiEmployeeVacationRecordServiceImpl.cancel(busiEmployeeVacationRecordBean);
				} else if ("AUDIT".equals(operType)) {
					busiEmployeeVacationRecordBean.setState(request.getParameter("STATE"));
					busiEmployeeVacationRecordBean.setCheck_employee_id(employeeId);
					busiEmployeeVacationRecordBean.setCheck_date(new Date());
					busiEmployeeVacationRecordServiceImpl.updateCheck(busiEmployeeVacationRecordBean);
				}
			} else if ("REPORT".equals(operType)) {
				long _employeeId = Long.parseLong(request.getParameter("EMPLOYEE_ID"));
				String type = request.getParameter("TYPE");
				String beginDate = request.getParameter("BEGIN_DATE");
				String endDate = request.getParameter("END_DATE");
				String numberOfDays = request.getParameter("NUMBER_OF_DAYS");
				busiEmployeeVacationRecordBean.setEmployee_id(_employeeId);
				busiEmployeeVacationRecordBean.setType(type);
				busiEmployeeVacationRecordBean.setBegin_date(beginDate);
				busiEmployeeVacationRecordBean.setEnd_date(endDate);
				busiEmployeeVacationRecordBean.setNumber_of_days(numberOfDays);
				busiEmployeeVacationRecordBean.setState("0");
				
				BusiEmployeeVacationRecordBean oldBean = busiEmployeeVacationRecordServiceImpl.getBean(_employeeId, beginDate);
				if (null != oldBean) {
					busiEmployeeVacationRecordBean.setRecord_id(oldBean.getRecord_id());
					busiEmployeeVacationRecordBean.setPlace(oldBean.getPlace());
					busiEmployeeVacationRecordBean.setReason(oldBean.getReason());
					busiEmployeeVacationRecordServiceImpl.updateRecord(busiEmployeeVacationRecordBean);
				} else {
					busiEmployeeVacationRecordServiceImpl.insert(busiEmployeeVacationRecordBean);
				}
			}
			rtnJson.put(BusiConstants.AjaxReturn.STATUS_CODE,
					BusiConstants.AjaxStatus.STATUS_SUCCESS);
			rtnJson.put(BusiConstants.AjaxReturn.STATUS_INFO, "提交成功");
		} catch (Exception ex) {
			ex.printStackTrace();
			String retMsg = ex.getCause() == null ? ex.getMessage() : ex
					.getCause().getMessage();
			rtnJson = ExceptionUtil.toJSON(retMsg, ex, request);
		} finally {
			response.setContentType("text/html;charset=utf-8");
			PrintWriter printWriter = response.getWriter();
			printWriter.write(rtnJson.toString());
			printWriter.flush();
			printWriter.close();
		}
	}
	
	@RequestMapping(value = "/audit")
	public ModelAndView audit(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap) {
		// 1、获取页面提交信息
		String employeeId = request.getParameter("EMPLOYEE_ID");
		// 2、获取人员休假记录
		if (!StringUtil.isBlank(employeeId)) {
			List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeansByEmployeeId(Long.parseLong(employeeId));
			modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
			modelMap.put("EMPLOYEE_ID", employeeId);
			modelMap.put("SELECT_EMPLOYEE", infoEmployeeServiceImpl
					.getBean(Long.parseLong(employeeId)));
		} else {
			List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeansByState("0");
			modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
		}
		List<InfoEmployeeBean> infoEmployeeBeanList = infoEmployeeServiceImpl
				.getBeanList();
		modelMap.put("EMPLOYEE_LIST", infoEmployeeBeanList);
		// 3、获取休假地列表
		modelMap.put("VACATION_PLACE", getVacationPlace());
		// 4、获取休假状态列表
		modelMap.put("VACATION_STATE", getVacationState());
		return new ModelAndView("vacation/audit", modelMap);
	}
	
	private List<SysParamBean> getVacationType() {
		return CacheUtil.getSysParamBeanList("VACATION", "TYPE");
	}
	
	private List<SysParamBean> getVacationMarkType() {
		return CacheUtil.getSysParamBeanList("VACATION", "TYPE_MARK");
	}
	
	private List<SysParamBean> getVacationPlace() {
		return CacheUtil.getSysParamBeanList("VACATION", "PLACE");
	}
	
	private List<SysParamBean> getVacationState() {
		return CacheUtil.getSysParamBeanList("VACATION", "STATE");
	}
	
	@RequestMapping(value = "/report")
	public ModelAndView report(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap) throws Exception {
		String reportMonth = request.getParameter("REPORT_MONTH");
		// 1、获取当前操作员信息
		InfoOperBeanExt infoOperBean = InfoOperUtil.getCurrOperInfo(request);
		// 2、获取人员信息
		InfoEmployeeBean infoEmployeeBean = infoOperBean.getInfoEmployeeBean();
		modelMap.put("SELECT_EMPLOYEE", infoEmployeeBean);
		// 3、获取请假类型列表
		modelMap.put("VACATION_MARK_TYPE", getVacationMarkType());
		// 4、获取月份列表
		modelMap.put("REPORT_MONTH_LIST", getReportMonthList());
		// 5、获取当前月份
		String currDate = mySQLDateServiceImpl.getSysdateStr();
		String currYearStr = currDate.split("-")[0];
		String currMonthStr = currDate.split("-")[1];
		if (!StringUtil.isBlank(reportMonth)) {
			currDate = reportMonth;
			currYearStr = currDate.substring(0, 4);
			currMonthStr = currDate.substring(5, 6);
		}
		
		int currYear = Integer.parseInt(currYearStr);
		int currMonth = Integer.parseInt(currMonthStr);
		SysParamBean sysParamBean = new SysParamBean();
		sysParamBean.setType_code("REPORT");
		sysParamBean.setParam_code("MONTH_LIST");
		String monthStr = "";
		if (String.valueOf(currMonth).length() == 1) {
			monthStr = "0" + currMonth;
		} else {
			monthStr = "" + currMonth;
		}
		sysParamBean.setColumn_value(currYear + monthStr);
		sysParamBean.setColumn_desc(currYear + "年" + monthStr + "月");
		modelMap.put("REPORT_CURR_MONTH", sysParamBean);
		// 6、获取当前月的天列表
		modelMap.put("DATE_LIST", infoDateService.getBeans(currYearStr, monthStr));
		// 7、获取休假记录
		List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeans(infoEmployeeBean.getEmployee_id(), currYearStr + monthStr);
		modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
		return new ModelAndView("vacation/report", modelMap);
	}
	
	@RequestMapping(value = "/check")
	public ModelAndView check(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap) throws Exception {
		String reportMonth = request.getParameter("REPORT_MONTH");
		// 1、获取人员信息
		List<InfoEmployeeBean> infoEmployeeBeanList = infoEmployeeServiceImpl
				.getBeanList();
		modelMap.put("EMPLOYEE_LIST", infoEmployeeBeanList);
		// 2、获取请假类型列表
		modelMap.put("VACATION_MARK_TYPE", getVacationMarkType());
		// 3、获取月份列表
		modelMap.put("REPORT_MONTH_LIST", getReportMonthList());
		// 4、获取当前月份
		String currDate = mySQLDateServiceImpl.getSysdateStr();
		String currYearStr = currDate.split("-")[0];
		String currMonthStr = currDate.split("-")[1];
		if (!StringUtil.isBlank(reportMonth)) {
			currDate = reportMonth;
			currYearStr = currDate.substring(0, 4);
			currMonthStr = currDate.substring(5, 6);
		}
		
		int currYear = Integer.parseInt(currYearStr);
		int currMonth = Integer.parseInt(currMonthStr);
		SysParamBean sysParamBean = new SysParamBean();
		sysParamBean.setType_code("REPORT");
		sysParamBean.setParam_code("MONTH_LIST");
		String monthStr = "";
		if (String.valueOf(currMonth).length() == 1) {
			monthStr = "0" + currMonth;
		} else {
			monthStr = "" + currMonth;
		}
		sysParamBean.setColumn_value(currYear + monthStr);
		sysParamBean.setColumn_desc(currYear + "年" + monthStr + "月");
		modelMap.put("REPORT_CURR_MONTH", sysParamBean);
		// 5、获取当前月的天列表
		// 3、获取月的天列表
		List<InfoDateBean> infoDateBeanList = infoDateService.getBeans(currYearStr, monthStr);
		modelMap.put("DATE_LIST", infoDateBeanList);
		// 6、获取休假记录
		List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeansByReportMonth(currYearStr + monthStr);
		modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
		// 将明细转为map，便于遍历查找	
		Map<String, List<BusiEmployeeVacationRecordBean>> resultMap = new HashMap<String, List<BusiEmployeeVacationRecordBean>>();
		for (BusiEmployeeVacationRecordBean recordBean : busiEmployeeVacationRecordBeanList) {
			String employeeIdStr = String.valueOf(recordBean.getEmployee_id());
			if (resultMap.containsKey(employeeIdStr)) {
				resultMap.get(employeeIdStr).add(recordBean);
			} else {
				List<BusiEmployeeVacationRecordBean> recordBeanLists = new ArrayList<BusiEmployeeVacationRecordBean>();
				recordBeanLists.add(recordBean);
				resultMap.put(employeeIdStr, recordBeanLists);
			}
		}
		
		for (int i = 0; i < infoEmployeeBeanList.size(); i ++) {
			// 填写人员信息
			InfoEmployeeBean infoEmployeeBean = infoEmployeeBeanList.get(i);
			String employeeIdStr = String.valueOf(infoEmployeeBean.getEmployee_id());
			float type11Cnt = 0;
			long type21Cnt = 0;
			float type29Cnt = 0;
			long type31Cnt = 0;
			float type39Cnt = 0;
			long type41Cnt = 0;
			long type51Cnt = 0;
			long type61Cnt = 0;
			long type71Cnt = 0;
			long type81Cnt = 0;
			List<BusiEmployeeVacationRecordBean> recordBeanLists = resultMap.get(employeeIdStr);
			for (int j = 0; j < infoDateBeanList.size(); j ++) {
				InfoDateBean infoDateBean = infoDateBeanList.get(j);
				if ("0".equals(infoDateBean.getDate_type())) {
					type11Cnt = type11Cnt + 1;
					if (null != recordBeanLists) {
						String day = infoDateBean.getDay();
						for (int k = 0; k < recordBeanLists.size(); k ++) {
							BusiEmployeeVacationRecordBean recordBean = recordBeanLists.get(k);
							String date = recordBean.getBegin_date().substring(8, 10);
							date = String.valueOf(Integer.parseInt(date));
							String type = recordBean.getType();
							if (day.equals(date)) {
								if ("21".equals(type)) {
									type21Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("22".equals(type) || "23".equals(type)) {
									type29Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("31".equals(type)) {
									type31Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("32".equals(type) || "33".equals(type)) {
									type39Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("41".equals(type)) {
									type41Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("51".equals(type)) {
									type51Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("61".equals(type)) {
									type61Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("71".equals(type)) {
									type71Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("81".equals(type)) {
									type81Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								}
								break;
							}
						}
					}
				}
			}
			type11Cnt = type11Cnt - type21Cnt - type29Cnt - type31Cnt - type39Cnt 
					  - type41Cnt - type51Cnt - type61Cnt - type71Cnt - type81Cnt;
			
			modelMap.put("sum_" + employeeIdStr + "_11", type11Cnt);
			modelMap.put("sum_" + employeeIdStr + "_21", type21Cnt);
			modelMap.put("sum_" + employeeIdStr + "_29", type29Cnt);
			modelMap.put("sum_" + employeeIdStr + "_31", type31Cnt);
			modelMap.put("sum_" + employeeIdStr + "_39", type39Cnt);
			modelMap.put("sum_" + employeeIdStr + "_41", type41Cnt);
			modelMap.put("sum_" + employeeIdStr + "_51", type51Cnt);
			modelMap.put("sum_" + employeeIdStr + "_61", type61Cnt);
			modelMap.put("sum_" + employeeIdStr + "_71", type71Cnt);
			modelMap.put("sum_" + employeeIdStr + "_81", type81Cnt);
		}

		return new ModelAndView("vacation/check", modelMap);
	}
	
	@RequestMapping(value = "/state")
	public ModelAndView state(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap) throws Exception {
		String reportMonth = request.getParameter("REPORT_MONTH");
		// 1、获取人员信息
		List<InfoEmployeeBean> infoEmployeeBeanList = infoEmployeeServiceImpl
				.getBeanList();
		modelMap.put("EMPLOYEE_LIST", infoEmployeeBeanList);
		// 2、获取请假类型列表
		modelMap.put("VACATION_MARK_TYPE", getVacationMarkType());
		// 3、获取月份列表
		modelMap.put("REPORT_MONTH_LIST", getReportMonthList());
		// 4、获取当前月份
		String currDate = mySQLDateServiceImpl.getSysdateStr();
		String currYearStr = currDate.split("-")[0];
		String currMonthStr = currDate.split("-")[1];
		if (!StringUtil.isBlank(reportMonth)) {
			currDate = reportMonth;
			currYearStr = currDate.substring(0, 4);
			currMonthStr = currDate.substring(5, 6);
		}
		
		int currYear = Integer.parseInt(currYearStr);
		int currMonth = Integer.parseInt(currMonthStr);
		SysParamBean sysParamBean = new SysParamBean();
		sysParamBean.setType_code("REPORT");
		sysParamBean.setParam_code("MONTH_LIST");
		String monthStr = "";
		if (String.valueOf(currMonth).length() == 1) {
			monthStr = "0" + currMonth;
		} else {
			monthStr = "" + currMonth;
		}
		sysParamBean.setColumn_value(currYear + monthStr);
		sysParamBean.setColumn_desc(currYear + "年" + monthStr + "月");
		modelMap.put("REPORT_CURR_MONTH", sysParamBean);
		// 5、获取当前月的天列表
		// 3、获取月的天列表
		List<InfoDateBean> infoDateBeanList = infoDateService.getBeans(currYearStr, monthStr);
		modelMap.put("DATE_LIST", infoDateBeanList);
		// 6、获取休假记录
		List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeansByReportMonth(currYearStr + monthStr);
		modelMap.put("VACATION_RECORD_LIST", busiEmployeeVacationRecordBeanList);
		// 将明细转为map，便于遍历查找	
		Map<String, List<BusiEmployeeVacationRecordBean>> resultMap = new HashMap<String, List<BusiEmployeeVacationRecordBean>>();
		for (BusiEmployeeVacationRecordBean recordBean : busiEmployeeVacationRecordBeanList) {
			String employeeIdStr = String.valueOf(recordBean.getEmployee_id());
			if (resultMap.containsKey(employeeIdStr)) {
				resultMap.get(employeeIdStr).add(recordBean);
			} else {
				List<BusiEmployeeVacationRecordBean> recordBeanLists = new ArrayList<BusiEmployeeVacationRecordBean>();
				recordBeanLists.add(recordBean);
				resultMap.put(employeeIdStr, recordBeanLists);
			}
		}
		
		for (int i = 0; i < infoEmployeeBeanList.size(); i ++) {
			// 填写人员信息
			InfoEmployeeBean infoEmployeeBean = infoEmployeeBeanList.get(i);
			String employeeIdStr = String.valueOf(infoEmployeeBean.getEmployee_id());
			float type11Cnt = 0;
			long type21Cnt = 0;
			float type29Cnt = 0;
			long type31Cnt = 0;
			float type39Cnt = 0;
			long type41Cnt = 0;
			long type51Cnt = 0;
			long type61Cnt = 0;
			long type71Cnt = 0;
			long type81Cnt = 0;
			List<BusiEmployeeVacationRecordBean> recordBeanLists = resultMap.get(employeeIdStr);
			for (int j = 0; j < infoDateBeanList.size(); j ++) {
				InfoDateBean infoDateBean = infoDateBeanList.get(j);
				if ("0".equals(infoDateBean.getDate_type())) {
					type11Cnt = type11Cnt + 1;
					if (null != recordBeanLists) {
						String day = infoDateBean.getDay();
						for (int k = 0; k < recordBeanLists.size(); k ++) {
							BusiEmployeeVacationRecordBean recordBean = recordBeanLists.get(k);
							String date = recordBean.getBegin_date().substring(8, 10);
							date = String.valueOf(Integer.parseInt(date));
							String type = recordBean.getType();
							if (day.equals(date)) {
								if ("21".equals(type)) {
									type21Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("22".equals(type) || "23".equals(type)) {
									type29Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("31".equals(type)) {
									type31Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("32".equals(type) || "33".equals(type)) {
									type39Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("41".equals(type)) {
									type41Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("51".equals(type)) {
									type51Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("61".equals(type)) {
									type61Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("71".equals(type)) {
									type71Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								} else if ("81".equals(type)) {
									type81Cnt += Float.parseFloat(recordBean.getNumber_of_days());
								}
								break;
							}
						}
					}
				}
			}
			type11Cnt = type11Cnt - type21Cnt - type29Cnt - type31Cnt - type39Cnt 
					  - type41Cnt - type51Cnt - type61Cnt - type71Cnt - type81Cnt;
			
			modelMap.put("sum_" + employeeIdStr + "_11", type11Cnt);
			modelMap.put("sum_" + employeeIdStr + "_21", type21Cnt);
			modelMap.put("sum_" + employeeIdStr + "_29", type29Cnt);
			modelMap.put("sum_" + employeeIdStr + "_31", type31Cnt);
			modelMap.put("sum_" + employeeIdStr + "_39", type39Cnt);
			modelMap.put("sum_" + employeeIdStr + "_41", type41Cnt);
			modelMap.put("sum_" + employeeIdStr + "_51", type51Cnt);
			modelMap.put("sum_" + employeeIdStr + "_61", type61Cnt);
			modelMap.put("sum_" + employeeIdStr + "_71", type71Cnt);
			modelMap.put("sum_" + employeeIdStr + "_81", type81Cnt);
		}

		return new ModelAndView("vacation/state", modelMap);
	}
	
	private List<SysParamBean> getReportMonthList() {
		List<SysParamBean> sysParamBeanList = new ArrayList<SysParamBean>();
		SysParamBean sysParamBean = new SysParamBean();
		String initYearStr = "2016";
		String initMonthStr = "03";
		int initYear = Integer.parseInt(initYearStr);
		int initMonth = Integer.parseInt(initMonthStr);
		String currDate = "";
		String afterMonthDate = "";
		try {
			currDate = mySQLDateServiceImpl.getSysdateStr();
			afterMonthDate = mySQLDateServiceImpl.getAfterMonthToday();
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String currYearStr = currDate.split("-")[0];
		String currMonthStr = currDate.split("-")[1];
		int currYear = Integer.parseInt(currYearStr);
		int currMonth = Integer.parseInt(currMonthStr);
		for (int year = currYear; year >= initYear; year--) {
			for (int month = 12; month >= 1; month--) {
				if (year == initYear) {
					if (month < initMonth) {
						continue;
					}
				}
				if (year == currYear) {
					if (month > currMonth) {
						continue;
					}
				}
				sysParamBean = new SysParamBean();
				sysParamBean.setType_code("REPORT");
				sysParamBean.setParam_code("MONTH_LIST");
				String monthStr = "";
				if (String.valueOf(month).length() == 1) {
					monthStr = "0" + month;
				} else {
					monthStr = "" + month;
				}
				sysParamBean.setColumn_value(year + monthStr);
				sysParamBean.setColumn_desc(year + "年" + monthStr + "月");
				sysParamBeanList.add(sysParamBean);
			}
		}
		String afterYearStr = afterMonthDate.split("-")[0];
		String afterMonthStr = afterMonthDate.split("-")[1];
		int afterYear = Integer.parseInt(afterYearStr);
		int afterMonth = Integer.parseInt(afterMonthStr);
		sysParamBean = new SysParamBean();
		sysParamBean.setType_code("REPORT");
		sysParamBean.setParam_code("MONTH_LIST");
		String monthStr = "";
		if (String.valueOf(afterMonth).length() == 1) {
			monthStr = "0" + afterMonth;
		} else {
			monthStr = "" + afterMonth;
		}
		sysParamBean.setColumn_value(afterYear + monthStr);
		sysParamBean.setColumn_desc(afterYear + "年" + monthStr + "月");
		sysParamBeanList.add(sysParamBean);
		return sysParamBeanList;
	}

	@RequestMapping(value = "/generatorExcel")
	public void generatorExcel(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		JSONObject json = new JSONObject();
		try {
			String reportMonth = request.getParameter("REPORT_MONTH");
			// 1、获取人员信息
			List<InfoEmployeeBean> infoEmployeeBeanList = infoEmployeeServiceImpl
					.getBeanList();
			// 2、获取请假类型列表
			List<SysParamBean> vacationMarkTypeList = getVacationMarkType();
			Map<String, String> markMap = new HashMap<String, String>();
			for (SysParamBean bean : vacationMarkTypeList) {
				markMap.put(bean.getColumn_value(), bean.getColumn_desc());
			}
			// 3、获取月的天列表
			List<InfoDateBean> infoDateBeanList = infoDateService.getBeans(reportMonth.substring(0, 4), reportMonth.substring(4, 6));
			// 4、获取休假记录
			// 查询明细
			List<BusiEmployeeVacationRecordBean> busiEmployeeVacationRecordBeanList = busiEmployeeVacationRecordServiceImpl.getBeansByReportMonth(reportMonth);
			// 将明细转为map，便于遍历查找	
			Map<String, List<BusiEmployeeVacationRecordBean>> resultMap = new HashMap<String, List<BusiEmployeeVacationRecordBean>>();
			for (BusiEmployeeVacationRecordBean recordBean : busiEmployeeVacationRecordBeanList) {
				String employeeIdStr = String.valueOf(recordBean.getEmployee_id());
				if (resultMap.containsKey(employeeIdStr)) {
					resultMap.get(employeeIdStr).add(recordBean);
				} else {
					List<BusiEmployeeVacationRecordBean> recordBeanLists = new ArrayList<BusiEmployeeVacationRecordBean>();
					recordBeanLists.add(recordBean);
					resultMap.put(employeeIdStr, recordBeanLists);
				}
			}
			// 目标文件，在模板中填充数据并生成 
			String outFileName = new String(reportMonth);  
			File targetFile = new File(outFileName);
			if(targetFile.exists())
				targetFile.delete();
			// 目标文件先引入template中的内容，并将以targetFile导出 
			WritableWorkbook target = Workbook.createWorkbook(targetFile);
			WritableSheet ws;
			Label label;
			//创建一个工作簿
			ws = target.createSheet(reportMonth.substring(0, 4) + "-" + reportMonth.substring(4, 6) + "考勤", 0);
			// 设置列宽
			ws.setColumnView(0, 6);
			ws.setColumnView(1, 6);
			ws.setColumnView(2, 12);
			int dateCol = infoDateBeanList.size();
			float workCol = 0;
			for (InfoDateBean infoDateBean :infoDateBeanList) {
				if ("0".equals(infoDateBean.getDate_type())) {
					workCol ++;
				}
			}
			for (int i = 0; i < dateCol ; i ++) {
				ws.setColumnView(i + 3, 5);
			}
			ws.setColumnView(3 + dateCol + 1, 6);
			ws.setColumnView(3 + dateCol + 2, 6);
			ws.setColumnView(3 + dateCol + 3, 6);
			ws.setColumnView(3 + dateCol + 4, 6);
			ws.setColumnView(3 + dateCol + 5, 6);
			ws.setColumnView(3 + dateCol + 6, 6);
			ws.setColumnView(3 + dateCol + 7, 6);
			ws.setColumnView(3 + dateCol + 8, 6);
			ws.setColumnView(3 + dateCol + 9, 6);
			ws.setColumnView(3 + dateCol + 10, 6);
			ws.setColumnView(3 + dateCol + 11, 12);
			/** 组织表头 **/
			/* 第一行 */
			// 第一格
			ws.mergeCells(0, 0, 2, 1);
			WritableFont wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.SINGLE, Colour.BLACK);
			WritableCellFormat wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.LIGHT_TURQUOISE); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(0, 0, "", wcfFormat);
			ws.addCell(label);
			// 第二格
			ws.mergeCells(3, 0, 3 + dateCol - 1, 0);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3, 0, "考勤明细", wcfFormat);
			ws.addCell(label);
			// 第三格
			ws.mergeCells(3 + dateCol, 0, 3 + dateCol + 10 - 1, 0);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol, 0, "月统计（天）", wcfFormat);
			ws.addCell(label);
			// 第四格
			ws.mergeCells(3 + dateCol + 10, 0, 3 + dateCol + 10, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 10, 0, "备注", wcfFormat);
			ws.addCell(label);
			/* 第二行 */
			// 第一列
			ws.mergeCells(3, 1, 3 + dateCol - 1, 1);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.RIGHT); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3, 1, "出勤△ 事假(√,上A,下P） 病假(〇,上㊤,下㊦) 年假□ 婚产M 旷工X 迟到L 早退E", wcfFormat);
			ws.addCell(label);
			// 第二列
			ws.mergeCells(3 + dateCol, 1, 3 + dateCol, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol, 1, "出勤", wcfFormat);
			ws.addCell(label);
			// 第三列
			ws.mergeCells(3 + dateCol + 1, 1, 3 + dateCol + 2, 1);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 1, 1, "事假", wcfFormat);
			ws.addCell(label);
			// 第四列
			ws.mergeCells(3 + dateCol + 3, 1, 3 + dateCol + 4, 1);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 3, 1, "病假", wcfFormat);
			ws.addCell(label);
			// 第五列
			ws.mergeCells(3 + dateCol + 5, 1, 3 + dateCol + 5, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 5, 1, "年假", wcfFormat);
			ws.addCell(label);
			// 第六列
			ws.mergeCells(3 + dateCol + 6, 1, 3 + dateCol + 6, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 6, 1, "婚产", wcfFormat);
			ws.addCell(label);
			// 第七列
			ws.mergeCells(3 + dateCol + 7, 1, 3 + dateCol + 7, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 7, 1, "旷工", wcfFormat);
			ws.addCell(label);
			// 第八列
			ws.mergeCells(3 + dateCol + 8, 1, 3 + dateCol + 8, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 8, 1, "迟到", wcfFormat);
			ws.addCell(label);
			// 第九列
			ws.mergeCells(3 + dateCol + 9, 1, 3 + dateCol + 9, 2);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE); //设置对齐方式
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(3 + dateCol + 9, 1, "早退", wcfFormat);
			ws.addCell(label);
			/* 第三行 */
			// 第一列
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE);
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.LIGHT_TURQUOISE); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(0, 2, "序号", wcfFormat);
			ws.addCell(label);
			label = new Label(1, 2, "工号", wcfFormat);
			ws.addCell(label);
			label = new Label(2, 2, "姓名", wcfFormat);
			ws.addCell(label);
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE);
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBackground(Colour.GREY_40_PERCENT); //背静色
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			for (int i = 0; i < dateCol ; i ++) {
				InfoDateBean infoDateBean = infoDateBeanList.get(i);
				label = new Label(3 + i, 2, infoDateBean.getDay(), wcfFormat);
				ws.addCell(label);
			}
			label = new Label(3 + dateCol + 1, 2, "天", wcfFormat);
			ws.addCell(label);
			label = new Label(3 + dateCol + 2, 2, "半天", wcfFormat);
			ws.addCell(label);
			label = new Label(3 + dateCol + 3, 2, "天", wcfFormat);
			ws.addCell(label);
			label = new Label(3 + dateCol + 4, 2, "半天", wcfFormat);
			ws.addCell(label);
			/** 组织表内容 **/
			for (int i = 0; i < infoEmployeeBeanList.size(); i ++) {
				// 填写人员信息
				InfoEmployeeBean infoEmployeeBean = infoEmployeeBeanList.get(i);
				String employeeIdStr = String.valueOf(infoEmployeeBean.getEmployee_id());
				wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
				wcfFormat = new WritableCellFormat(wfc);
				wcfFormat.setAlignment(Alignment.CENTRE);
				wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
				wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
				label = new Label(0, 3 + i, String.valueOf(i + 1), wcfFormat);
				ws.addCell(label);
				label = new Label(1, 3 + i, String.valueOf(infoEmployeeBean.getEmployee_no()), wcfFormat);
				ws.addCell(label);
				label = new Label(2, 3 + i, infoEmployeeBean.getEmployee_name(), wcfFormat);
				ws.addCell(label);
				// 填写考核记录
				float type11Cnt = 0;
				long type21Cnt = 0;
				float type29Cnt = 0;
				long type31Cnt = 0;
				float type39Cnt = 0;
				long type41Cnt = 0;
				long type51Cnt = 0;
				long type61Cnt = 0;
				long type71Cnt = 0;
				long type81Cnt = 0;
				List<BusiEmployeeVacationRecordBean> recordBeanLists = resultMap.get(employeeIdStr);
				for (int j = 0; j < infoDateBeanList.size(); j ++) {
					InfoDateBean infoDateBean = infoDateBeanList.get(j);
					if ("0".equals(infoDateBean.getDate_type())) {
						type11Cnt = type11Cnt + 1;
						wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
						wcfFormat = new WritableCellFormat(wfc);
						wcfFormat.setAlignment(Alignment.CENTRE);
						wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
						wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
						label = new Label(3 + j, 3 + i, "△", wcfFormat);
						ws.addCell(label);
						if (null != recordBeanLists) {
							String day = infoDateBean.getDay();
							for (int k = 0; k < recordBeanLists.size(); k ++) {
								BusiEmployeeVacationRecordBean recordBean = recordBeanLists.get(k);
								String date = recordBean.getBegin_date().substring(8, 10);
								date = String.valueOf(Integer.parseInt(date));
								String type = recordBean.getType();
								if (day.equals(date)) {
									label = new Label(2 + Integer.parseInt(day), 3 + i, markMap.get(recordBean.getType()), wcfFormat);
									ws.addCell(label);
									if ("21".equals(type)) {
										type21Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("22".equals(type) || "23".equals(type)) {
										type29Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("31".equals(type)) {
										type31Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("32".equals(type) || "33".equals(type)) {
										type39Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("41".equals(type)) {
										type41Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("51".equals(type)) {
										type51Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("61".equals(type)) {
										type61Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("71".equals(type)) {
										type71Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									} else if ("81".equals(type)) {
										type81Cnt += Float.parseFloat(recordBean.getNumber_of_days());
									}
									break;
								}
							}
						}
					} else {
						wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
						wcfFormat = new WritableCellFormat(wfc);
						wcfFormat.setAlignment(Alignment.CENTRE);
						wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
						wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
						wcfFormat.setBackground(Colour.GREY_25_PERCENT); //背静色
						label = new Label(3 + j, 3 + i, "", wcfFormat);
						ws.addCell(label);
					}
				}
				type11Cnt = type11Cnt - type21Cnt - type29Cnt - type31Cnt - type39Cnt 
						  - type41Cnt - type51Cnt - type61Cnt - type71Cnt - type81Cnt;
				// 填写统计天数
				label = new Label(2 + dateCol + 1, 3 + i, String.valueOf(type11Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 2, 3 + i, String.valueOf(type21Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 3, 3 + i, String.valueOf(type29Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 4, 3 + i, String.valueOf(type31Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 5, 3 + i, String.valueOf(type39Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 6, 3 + i, String.valueOf(type41Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 7, 3 + i, String.valueOf(type51Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 8, 3 + i, String.valueOf(type61Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 9, 3 + i, String.valueOf(type71Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 10, 3 + i, String.valueOf(type81Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 11, 3 + i, "", wcfFormat);
				ws.addCell(label);
			}
			// 合计
			wfc = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			wcfFormat = new WritableCellFormat(wfc);
			wcfFormat.setAlignment(Alignment.CENTRE);
			wcfFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
			wcfFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			label = new Label(0, 3 + infoEmployeeBeanList.size(), "", wcfFormat);
			ws.addCell(label);
			label = new Label(1, 3 + infoEmployeeBeanList.size(), "", wcfFormat);
			ws.addCell(label);
			label = new Label(2, 3 + infoEmployeeBeanList.size(), "合计", wcfFormat);
			ws.addCell(label);
			for (int j = 0; j < infoDateBeanList.size(); j ++) {
				InfoDateBean infoDateBean = infoDateBeanList.get(j);
				String day = infoDateBean.getDay();
				int cnt = 0;
				for (int k = 0; k < busiEmployeeVacationRecordBeanList.size(); k ++) {
					BusiEmployeeVacationRecordBean recordBean = busiEmployeeVacationRecordBeanList.get(k);
					String date = recordBean.getBegin_date().substring(8, 10);
					date = String.valueOf(Integer.parseInt(date));
					String type = recordBean.getType();
					if (day.equals(date)) {
						cnt ++;
					}
				}
				label = new Label(2 + Integer.parseInt(day), 3 + infoEmployeeBeanList.size(), "" + cnt, wcfFormat);
				ws.addCell(label);
				float type11Cnt = 0;
				long type21Cnt = 0;
				float type29Cnt = 0;
				long type31Cnt = 0;
				float type39Cnt = 0;
				long type41Cnt = 0;
				long type51Cnt = 0;
				long type61Cnt = 0;
				long type71Cnt = 0;
				long type81Cnt = 0;
				for (int k = 0; k < busiEmployeeVacationRecordBeanList.size(); k ++) {
					BusiEmployeeVacationRecordBean recordBean = busiEmployeeVacationRecordBeanList.get(k);
					String date = recordBean.getBegin_date().substring(8, 10);
					date = String.valueOf(Integer.parseInt(date));
					String type = recordBean.getType();
					if (day.equals(date)) {
						cnt ++;
					}
					if ("21".equals(type)) {
						type21Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("22".equals(type) || "23".equals(type)) {
						type29Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("31".equals(type)) {
						type31Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("32".equals(type) || "33".equals(type)) {
						type39Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("41".equals(type)) {
						type41Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("51".equals(type)) {
						type51Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("61".equals(type)) {
						type61Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("71".equals(type)) {
						type71Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					} else if ("81".equals(type)) {
						type81Cnt += Float.parseFloat(recordBean.getNumber_of_days());
					}
				}
				type11Cnt = workCol * infoEmployeeBeanList.size()
						  - type21Cnt - type29Cnt - type31Cnt - type39Cnt 
						  - type41Cnt - type51Cnt - type61Cnt - type71Cnt - type81Cnt;
				// 填写统计天数
				label = new Label(2 + dateCol + 1, 3 + infoEmployeeBeanList.size(), String.valueOf(type11Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 2, 3 + infoEmployeeBeanList.size(), String.valueOf(type21Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 3, 3 + infoEmployeeBeanList.size(), String.valueOf(type29Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 4, 3 + infoEmployeeBeanList.size(), String.valueOf(type31Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 5, 3 + infoEmployeeBeanList.size(), String.valueOf(type39Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 6, 3 + infoEmployeeBeanList.size(), String.valueOf(type41Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 7, 3 + infoEmployeeBeanList.size(), String.valueOf(type51Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 8, 3 + infoEmployeeBeanList.size(), String.valueOf(type61Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 9, 3 + infoEmployeeBeanList.size(), String.valueOf(type71Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 10, 3 + infoEmployeeBeanList.size(), String.valueOf(type81Cnt), wcfFormat);
				ws.addCell(label);
				label = new Label(2 + dateCol + 11, 3 + infoEmployeeBeanList.size(), "", wcfFormat);
				ws.addCell(label);
			}
			//关闭文件 
			target.write();
			target.close();
			json.put(BusiConstants.AjaxReturn.STATUS_CODE,
					BusiConstants.AjaxStatus.STATUS_SUCCESS);
			json.put(BusiConstants.AjaxReturn.STATUS_INFO, "Excel生成成功");
			json.put(BusiConstants.AjaxReturn.RETURN_INFO, outFileName);
		} catch (Exception e) {
			String retMsg = e.getCause() == null ? e.getMessage() : e
					.getCause().getMessage();
			retMsg = StringUtil.isBlank(retMsg) ? "Excel生成失败" : retMsg;
			json = ExceptionUtil.toJSON(retMsg, e, request);
		} finally {
			response.setContentType("text/html;charset=utf-8");
			PrintWriter printWriter = response.getWriter();
			printWriter.write(json.toString());
			printWriter.flush();
			printWriter.close();
		}
	}
	
	@RequestMapping(value = "/downloadExcel")
	public void downloadExcel(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		JSONObject json = new JSONObject();
		try {
			String rometeFileName = request.getParameter("_FILE_NAME");
			String localFileName = "软件工程研究院考勤" + rometeFileName + ".xls";
			// 下载Excel文件
			ExcelUtil.download(response, localFileName, "",
					rometeFileName);
			json.put(BusiConstants.AjaxReturn.STATUS_CODE,
					BusiConstants.AjaxStatus.STATUS_SUCCESS);
			json.put(BusiConstants.AjaxReturn.STATUS_INFO, "Excel下载成功");
			json.put(BusiConstants.AjaxReturn.RETURN_INFO, localFileName);
		} catch (Exception e) {
			String retMsg = e.getCause() == null ? e.getMessage() : e
					.getCause().getMessage();
			retMsg = StringUtil.isBlank(retMsg) ? "Excel下载失败" : retMsg;
			json = ExceptionUtil.toJSON(retMsg, e, request);
		} finally {
			response.setContentType("text/html;charset=utf-8");
			PrintWriter printWriter = response.getWriter();
			printWriter.write(json.toString());
			printWriter.flush();
			printWriter.close();
		}
	}
}
